<%@ page import="custom.*, org.json.simple.*, java.util.*, java.sql.*, javax.sql.*, javax.naming.*" %>

<%	
	// create a JSON object
	JSONObject result = new JSONObject();
	// retrieve the applicant data from the database
	int id = Integer.parseInt(request.getParameter("applicant_id"));
	//int id = 3;
	InitialContext cxt = new InitialContext();
	DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
	Connection conn = ds.getConnection();
	 conn.setAutoCommit(false);
	//basic info
	PreparedStatement stmt = conn.prepareStatement("SELECT first_name, middle_name, last_name, CC.country AS cc_country, CR.country AS cr_country, street, city, state, zip, area_code, phone, country_phone_code, specialization  FROM applicants AS A, countries AS CC, countries AS CR,  specializations AS S WHERE A.id = ? AND A.ccid = CC.id AND A.crid = CR.id AND A.specialization_id = S.id");
	stmt.setInt(1, id);
	ResultSet rs = stmt.executeQuery();
	if(rs.next() ) {
		result.put("first_name", rs.getString("first_name") );
		result.put("middle_name", rs.getString("middle_name") );
		result.put("last_name", rs.getString("last_name") );
		result.put("cc_country", rs.getString("cc_country") );
		result.put("cr_country", rs.getString("cr_country") );
		result.put("street", rs.getString("street") );
		result.put("city", rs.getString("city") );
		result.put("state", rs.getString("state") );
		result.put("zip", rs.getString("zip") );
		result.put("area_code", rs.getString("area_code") );
		result.put("phone", rs.getString("phone") );
		result.put("country_phone_code", rs.getString("country_phone_code") );
		result.put("specialization", rs.getString("specialization") );
	}
	
	// degree info
	JSONArray degrees = new JSONArray();
	stmt = conn.prepareStatement("SELECT university, major, graduation_date, gpa, degree_type FROM applicants AS A, degrees AS D, majors AS M, universities AS U WHERE A.id = ? AND A.id = D.applicant_id AND D.university_id = U.id AND D.major_id = M.id ");
	stmt.setInt(1, id);
	rs = stmt.executeQuery();
	while(rs.next() ) {
		JSONObject degree = new JSONObject();
		degree.put("university", rs.getString("university"));
		degree.put("major", rs.getString("major"));
		degree.put("graduation_date", rs.getString("graduation_date"));
		degree.put("gpa", rs.getString("gpa"));
		degree.put("degree_type", rs.getString("degree_type"));
		// add the JSON object into the JSONArray
		degrees.add(degree);
 	}
	result.put("degrees", degrees);
	conn.commit();
	rs.close();
	stmt.close();
	conn.close();
	
	out.print(result);
%>